package com.studyv3.test.mapper;

import com.studyv3.common.core.mapper.BaseMapperPlus;
import com.studyv3.test.domain.Batch;
import com.studyv3.test.domain.vo.BatchVo;
import com.studyv3.test.domain.vo.EchartVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
* @author 侯杰
* @description 针对表【lb_batch(lb_采购批次)】的数据库操作Mapper
* @createDate 2024-05-07 10:04:41
* @Entity com.studyv3.test.domain.Batch
*/
public interface BatchMapper extends BaseMapperPlus<BatchMapper, Batch, BatchVo> {

    void batchInsert(@Param("list") List<Batch> cachedDataList);

    List<Batch> selectAllList(@Param("a")String a, @Param("b")String b, @Param("c")String c);

    @Update("update lb_batch set io = case when io = 1 then 0 when io = 0 then 1 end,update_time=now() where id = #{id} ")
    void change(Integer id);


    @Select("SELECT a.*,b.balance FROM ( SELECT * FROM lb_batch ) a " +
            "LEFT JOIN (SELECT a.id, a.budget - SUM( CASE WHEN b.flag = 1 THEN b.price ELSE 0 END ) AS balance " +
            "FROM lb_batch a " +
            "LEFT JOIN lb_apply_product b ON a.id = b.batch_id AND b.deleted = 0 GROUP BY a.id,a.budget) b " +
            "ON a.id = b.id where a.id = #{id}")
    Batch selectId(Integer id);

    @Select("SELECT b.name bname,SUM(price) amount FROM lb_apply_product a LEFT JOIN lb_batch b on a.batch_id = b.id WHERE a.flag = 1\n" +
            "GROUP BY b.name order by b.name")
    List<EchartVo> elist();
}




